CREATE       procedure amsp_ICMove
    @InSourceNodeID    numeric,
    @InDestNodeID    numeric
AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** Moves a node in the Interest_Category tree (InSourceNodeID) to be a sibling directly
  ** beneath the destination (InDestNodeID) node.
  **
  ** INPUTS:
  ** -------
  **   @InSourceNodeID    Source node being moved
  **   @InDestNodeID    Destination node being moved beneath
  **
  ** OUTPUTS:
  ** -----------
  **   none
  **
  ** NOTES:
  ** ------
  **   1) Interest_Category table has AncestorOrder but this stored procedure is wriiten to
  **      allow only SortOrder to be used to fully sort all interest categories (i.e. sort order
  **      covers all IC's, not just IC's for the same ancestor).  This will allow AncestorOrder to
  **      be removed easily (in the future) so that IC tree and Nav_Menu tree follow the same structure.
  **
  ** HISTORY:
  ** --------
  **   03/21/2003    N.Malhotra    Initial Version Created
  **
  */

  Declare  
    @DestSortOrder        numeric,
    @DestDepth            numeric,
    @DestParentID        numeric,
    @DestAncestorID        numeric,
    @SourceSortOrder        numeric,
    @SourceDepth        numeric,
    @SourceParentID        numeric,
    @SourceAncestorID        numeric,
    @InvalidCount        int,
    @Counter            int

  /*
  ** After confirming there is something to move, begin processing
  */

  IF @InSourceNodeID != @InDestNodeID BEGIN

    SET @InvalidCount = 0

    BEGIN TRANSACTION

    /*
    ** Let's find out about our destination node
    */

    SELECT
      @DestSortOrder  = SortOrder,
      @DestDepth      = CategoryDepth,
      @DestParentID   = ParentCategoryID,
      @DestAncestorID = AncestorCategoryID
    FROM
      Interest_Category
    WHERE
      InterestCategoryID = @InDestNodeID

    /*
    ** Let's find out about our source node
    */

    SELECT
      @SourceSortOrder  = SortOrder,
      @SourceDepth      = CategoryDepth,
      @SourceParentID   = ParentCategoryID,
      @SourceAncestorID = AncestorCategoryID
    FROM
      Interest_Category
    WHERE
      InterestCategoryID = @InSourceNodeID

    IF @DestAncestorID = @SourceAncestorID BEGIN

      /*
      ** We need to make sure the destination node is not a direct decendant of the source node.
      ** If it is, the move operation would create a circular relationship and so it is invalid
      */

      SET @Counter = 0

      CREATE TABLE #TEMP (ChildID numeric)

      INSERT INTO #TEMP
      SELECT InterestCategoryID
        FROM Interest_Category
       WHERE ParentCategoryID = @InSourceNodeID

      WHILE (@Counter < 50 AND @@ROWCOUNT > 0) BEGIN

        SET @Counter = @Counter + 1
   
        INSERT INTO #TEMP
        SELECT InterestCategoryID
          FROM Interest_Category
         WHERE ParentCategoryID IN (SELECT ChildID FROM #TEMP)
           AND InterestCategoryID NOT IN (SELECT ChildID FROM #TEMP)
      END

      SELECT @InvalidCount = Count(*)
        FROM #TEMP
       WHERE ChildID = @InDestNodeID

      IF @InvalidCount > 0  BEGIN
        RAISERROR ('You may not move nodes within the same level 1 hierarchy', 16, 1)
      END
    END

    IF @InvalidCount = 0 BEGIN

      /*
      ** Simply adjust the parent pointer
      */
  
      UPDATE
        Interest_Category
      SET
        ParentCategoryID   = @DestParentID,
    SortOrder = @DestSortOrder + 1
      WHERE
        InterestCategoryID = @InSourceNodeID

      /*
      ** amsp_FixTree will set the AncestorID, SortOrder, CategoryDepth for the entire tree.
      */
    
      EXEC amsp_ICFixTree

    END

    COMMIT TRANSACTION

  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_ICMove] TO [IMIS]
GO